package cn.kys.modules.system.service.impl;


import cn.hutool.json.JSONUtil;
import cn.kys.common.utils.*;
import cn.kys.modules.security.security.vo.JwtUser;
import cn.kys.modules.system.domain.GeomEntity;
import cn.kys.modules.system.pojo.SubtaskGeom;
import cn.kys.modules.system.pojo.SubtaskPolygon;
import cn.kys.modules.system.repository.MobileMapper;
import cn.kys.modules.system.repository.ToolsResposity;
import cn.kys.modules.system.service.MobileService;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.gridfs.GridFSBucket;
import com.mongodb.client.gridfs.GridFSBuckets;
import com.mongodb.client.gridfs.GridFSDownloadStream;
import com.mongodb.client.gridfs.GridFSFindIterable;
import com.mongodb.client.gridfs.model.GridFSFile;


import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.bson.Document;
import org.bson.types.ObjectId;

import org.geotools.geometry.jts.JTSFactoryFinder;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.web.ServerProperties;
import org.springframework.boot.autoconfigure.web.embedded.EmbeddedWebServerFactoryCustomizerAutoConfiguration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.mongodb.gridfs.GridFsResource;
import org.springframework.data.mongodb.gridfs.GridFsTemplate;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
//import sun.misc.BASE64Encoder;
import org.apache.commons.codec.binary.Base64;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.UnknownHostException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

import static cn.kys.common.utils.Utils.convertWKTtoGeoJSON;

@Primary
@Service
public class MobileServiceImplzxw implements MobileService {
    @Value("${file.shpUpdate}")
    private String shpUpdate;

    @Value("${file.videoFolder}")
    private String videoFolder;

    @Value("${file.tempJpgFile}")
    private String tempJpgFile;

    @Value("${file.localStoreJpgFile}")
    private String localStoreJpgFile;


    @Autowired
    private ToolsResposity toolsResposity;

    @Autowired
    private ServerProperties serverProperties;

    @Autowired
    MongoTemplate mongoTemplate;

    @Autowired
    GridFsTemplate gridFsTemplate;

    @Autowired(required = false)
    private EmbeddedWebServerFactoryCustomizerAutoConfiguration customizationBean;

    @Autowired
    private UserDetailsService userDetailsService;

    @Autowired
    private MobileMapper mobileMapper;

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

    @Override
    public List getTasks() {
//3129
        List list = new ArrayList();
        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
//        Long userid = 3129L;
//        String sql = "SELECT * FROM public.t_task where id in (SELECT taskid FROM public.t_subtask where userid = " + userid + " and status = '已开始') and status !='未开始'";
        String sql = String.format("SELECT * FROM public.t_task as task where id in\n" +
                "(SELECT taskid FROM t_subtask as subtask where subtask.userid = %d  and subtask.is_del = 0" +
                "and subtask.status !='未开始' and subtask.status !='已结束' )\n" +
                "and task.status !='未开始'", userid);//  不要表t_subtask中未开始和已结束的子任务
        List listTask = toolsResposity.exeSqlList(sql);
//问问梦然 monggoDB中已经提交的斑块 应该和数据库中t_subtask_geom中字段check_status的状态同步  这样就简单多了
        for (int i = 0; i < listTask.size(); i++) {
            Map taskMap = (Map) listTask.get(i);
            String taskIdStr = taskMap.get("id").toString();
            System.out.println(taskIdStr);//184
            List<SubtaskGeom> subtaskGeomList = toolsResposity.getTasks(Integer.parseInt(taskMap.get("id").toString()));
            if (subtaskGeomList.size() > 0) {
                list.add(taskMap);
            }
            //pg数据库t_subtask中找到 taskid  对应的存在不是已经提交的都返回去。

          /*  //这个globle_taskid可能不存在于monggoDb中，意思是任务已经开始,但是monggoDb没有数据呢  也要把任务返回去
            Criteria criteriaIs = Criteria.where("metadata.globle_taskid").is(taskIdStr);
            Query queryIs = new Query(criteriaIs);
            boolean existedqueryIs = mongoTemplate.exists(queryIs, JSONObject.class, "tableEntity");
            System.out.println(existedqueryIs);
            if (existedqueryIs == false) {
                String sql_subtask = String.format("SELECT * FROM public.t_subtask where taskid = %d and userid = %d", Integer.parseInt(taskIdStr), userid);
                Map subTaskMap = toolsResposity.exeSql(sql_subtask);
                String status = subTaskMap.get("status").toString();
                taskMap.put("subtaskStatus", status);
                list.add(taskMap);
            }

            //判断该手机采集员 在taskId下，没有 提交 的斑块
            Criteria criteria = Criteria.where("metadata.globle_taskid").is(taskIdStr)
                    .and("metadata.globle_status").ne("已提交")
                    .and("metadata.globle_userid").is(userid);
            Query query = new Query(criteria);
            boolean existed = mongoTemplate.exists(query, JSONObject.class, "tableEntity");
            //该手机采集员存在  没有提交的斑块  就返回去该任务
            if (existed == true) {
                String sql_subtask = "SELECT * FROM public.t_subtask where taskid = " + Integer.parseInt(taskIdStr) + " and userid = " + userid;
                Map subTaskMap = toolsResposity.exeSql(sql_subtask);
                String status = subTaskMap.get("status").toString();
                taskMap.put("subtaskStatus", status);
                list.add(taskMap);
            } else {
                continue;
            }*/

        }

        return list;
    }

    @Override
    public List getTasksPC() {
        List list = new ArrayList();
        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
        String sql = "SELECT * FROM public.t_task where id in (SELECT taskid FROM public.t_subtask where userid = " + userid + " and is_del = 0 ) and status !='未开始'";
        List listTask = toolsResposity.exeSqlList(sql);
        for (int i = 0; i < listTask.size(); i++) {
            Map taskMap = (Map) listTask.get(i);
            int taskId = Integer.parseInt(taskMap.get("id").toString());
            String sql_subtask = "SELECT * FROM public.t_subtask where taskid = " + taskId + " and userid = " + userid + "and is_del = 0 ";
            Map subTaskMap = toolsResposity.exeSql(sql_subtask);
            String status = subTaskMap.get("status").toString();
            taskMap.put("subtaskStatus", status);
            list.add(taskMap);
        }
        return list;
    }

    @Override
    public void submitTableContent(String subtaskid, String edit_geoid, String status, String content) {
        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

        String sql = "select taskid from t_subtask where id = '" + subtaskid + "'" + "and is_del = 0 ";
        Map taskIdMap = toolsResposity.exeSql(sql);
        String taskIdStr = taskIdMap.get("taskid").toString();

        //保存内容到mongodb
        try {
            //存储表单信息
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid);
            criteria.and("metadata.globle_userid").is(userid);
            Query query = new Query(criteria);
            boolean existed = mongoTemplate.exists(query, JSONObject.class, "tableEntity");

            if (existed) {
                //删除原来的
                mongoTemplate.findAllAndRemove(query, JSONObject.class, "tableEntity");
            }
            JSONObject jsonObject = (JSONObject) JSON.parse(content);

            Map metadata = new HashMap<>();
            metadata.put("globle_userid", userid);
            metadata.put("globle_subtaskid", subtaskid);
            metadata.put("globle_edit_geoid", edit_geoid);
            metadata.put("globle_status", status);
            metadata.put("globle_taskid", taskIdStr);


            jsonObject.put("metadata", metadata);

            mongoTemplate.save(jsonObject, "tableEntity");


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private List filterList(List list) {

        List returnList = new ArrayList();

        for (int i = 0; i < list.size(); i++) {
            Map returnMap = (Map) list.get(i);

            String st_astext = returnMap.get("polygon").toString();

            List listPolygon = new ArrayList();

            if (st_astext.contains("MULTIPOLYGON")) {
                st_astext = st_astext.substring(16, st_astext.length() - 3);
            } else {
                st_astext = st_astext.substring(9, st_astext.length() - 2);
            }

            String[] polygons = st_astext.split("\\)\\), \\(\\(");

            for (int j = 0; j < polygons.length; j++) {
                String polygon = polygons[j].trim();
                if (polygon.contains("),(")) {
                    String[] polygonHole = polygon.split("\\),\\(");
                    listPolygon.add(polygonHole);
                } else {
                    listPolygon.add(polygon);
                }
            }

            returnMap.put("geo", listPolygon);

            returnList.add(returnMap);
        }

        return returnList;

    }

    @Override
    public String submitTableFile(String subtaskid, String globle_edit_geoid, String formcontentname, MultipartFile uploadFile, String alpha
            , String beta
            , String gamma
            , String lat
            , String lon) {

        String filename = null;

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
        try {
            //获取文件名
            String fileName = uploadFile.getOriginalFilename();
            //获取文件后缀名
            String suffixName = fileName.substring(fileName.lastIndexOf("."));
            //重新生成文件名
            String newfileName = UUID.randomUUID() + suffixName;
            //添加日期目录
            String format = sdf.format(new Date());
            // 使用时间戳确保唯一
            String uniqueFolderName = format + "_" + System.currentTimeMillis();
            File fileDir = new File(tempJpgFile + uniqueFolderName);
            //指定本地文件夹存储图片
            String filePath = fileDir.getAbsolutePath() + File.separator;

            File file = new File(filePath, newfileName);

            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            try {
                //图片临时保存在tempJpgFile中
                file.createNewFile();
                uploadFile.transferTo(file);
                //图片上传到monggoDb中
                filename = saveFileToMonggoDb(file, UUIDStringUtil.getUUIDString(), formcontentname, userid + "", subtaskid, globle_edit_geoid, alpha, beta, gamma, lat, lon);


            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return filename;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO   改原来bug  学习到删除文件前要关闭流
     * @Date 15:41 2024/1/11
     **/
    public String saveFileToMonggoDb(File file, String fileid, String formcontentname, String globle_userid, String globle_subtaskid, String globle_edit_geoid,
                                     String alpha, String beta, String gamma, String lat, String lon) {
        String filenameStr = null;
        try {
            // 设置meta数据值
            Map<String, String> metaData = new HashMap<>();
            metaData.put("globle_userid", globle_userid);
            metaData.put("globle_subtaskid", globle_subtaskid);
            metaData.put("globle_edit_geoid", globle_edit_geoid);
            metaData.put("globle_formcontentname", formcontentname);
            metaData.put("filename", fileid);
            metaData.put("alpha", alpha);
            metaData.put("beta", beta);
            metaData.put("gamma", gamma);
            metaData.put("lat", lat);
            metaData.put("lon", lon);

            // 创建SimpleDateFormat对象并指定日期格式
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 获取当前系统时间
            Date currentDate = new Date();
            // 格式化日期
            String formattedDate = sdf.format(currentDate);
            metaData.put("time", formattedDate);

            //与根据File类对象的所代表的实际文件建立链接创建fileInputStream对象
            FileInputStream fileInputStream = new FileInputStream(file);
            // 获取文件的源名称
            String fileName = file.getAbsoluteFile().getName();
            filenameStr = fileName;
            // 进行文件存储
            ObjectId objectId = gridFsTemplate.store(fileInputStream, fileName, metaData);

            System.out.println("照片存储到monggoDB----" + objectId);

            // 关闭文件输入流,必须关闭 否则无法删除本地文件 zxw
            fileInputStream.close();

            if (file.getParentFile().isDirectory()) {
                deleteFiles(file.getParentFile());
                System.out.println("成功删除临时文件夹：" + file.getParentFile());
            } else {
                System.out.println("临时文件不存在：" + file.getParentFile());
            }
          /*  //查询filename,根据objectId
            Criteria criteria = Criteria.where("_id").is(objectId.toString());
            Query query = new Query(criteria);
            GridFSFile gridFSFile = gridFsTemplate.findOne(query);
            Map map = gridFSFile.getMetadata();
            filename = map.get("filename").toString();*/

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("存储文件时发生错误！！！");
        }

        return filenameStr;
    }

    public void deleteFiles(File file) {
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            if (files != null) {
                for (File f : files) {
                    deleteFiles(f);
                }
            }
        }
        file.delete();
    }


    /**
     * @return
     * @Author zxw
     * @Description //TODO 上传视频
     * @Date 2023/10/24  22:19
     **/
    @Override
    public String submitVideoFile(String subtaskid, String globle_edit_geoid, String formcontentname, MultipartFile[] uploadFiles) throws UnknownHostException {

   /*     JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();*/

        String filename = null;
        String filePath = videoFolder + subtaskid + File.separator + globle_edit_geoid;

        try {
            for (MultipartFile uploadFile : uploadFiles) {
                //获取文件名
                String fileName = uploadFile.getOriginalFilename();
                //获取文件后缀名
                String suffixName = fileName.substring(fileName.lastIndexOf("."));
                //重新生成文件名
                fileName = UUID.randomUUID() + suffixName;
                String fullFilePath = filePath + File.separator + fileName;
                File file = new File(fullFilePath);
                if (!file.getParentFile().exists()) {
                    file.getParentFile().mkdirs();
                }
                try {
                    //视频保存
                    file.createNewFile();
                    //视频保存到本地磁盘
                    uploadFile.transferTo(file);
                    //把视频名称保存到数据库
                    filename = saveVideoFileToPgDb(file, subtaskid, globle_edit_geoid, formcontentname);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return filename;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO
     * @Date 16:46 2024/1/11
     **/
    private String saveVideoFileToPgDb(File file, String subtaskid, String globle_edit_geoid, String formcontentname) {
        String fileName = file.getAbsoluteFile().getName();
        try {
            String query = String.format("select id from t_video_info where subtaskid= '%s' AND globle_edit_geoid='%s' AND file_name='%s' AND formcontentname='%s'", subtaskid, globle_edit_geoid, fileName, formcontentname);
            Map<Integer, Long> map = toolsResposity.exeSql(query);

            if (map != null) {
                Long id = map.get("id");
                String saveVideoName = String.format("UPDATE t_video_info SET file_name='%s',subtaskid='%s', globle_edit_geoid='%s', formcontentname='%s'  WHERE id=%d ", fileName, subtaskid, globle_edit_geoid, formcontentname, id);
                toolsResposity.exeSql(saveVideoName);
            } else {

                // 创建SimpleDateFormat对象并指定日期格式
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                // 获取当前系统时间
                Date currentDate = new Date();
                // 格式化日期
                String formattedDate = sdf.format(currentDate);

                String saveVideoName = String.format("INSERT INTO t_video_info(subtaskid,globle_edit_geoid,file_name,formcontentname,update_time,time) VALUES ('%s','%s', '%s','%s',now(),'%s')", subtaskid, globle_edit_geoid, fileName, formcontentname, formattedDate);
                toolsResposity.exeSql(saveVideoName);
            }


        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("存储文件名字时发生错误！！！");
        }
        return fileName;
    }

    @Override
    public List queryVideoFile(String subtaskid, String globle_edit_geoid) {
        List list = new ArrayList();
        try {
            //根据id查询视频名字
            String queryVideoName = String.format("select file_name,formcontentname,time from t_video_info where subtaskid= '%s' and globle_edit_geoid='%s' ", subtaskid, globle_edit_geoid);
            List<Map> currentVideoNameList = toolsResposity.exeSqlList(queryVideoName);

            if (null != currentVideoNameList && currentVideoNameList.size() > 0) {
                for (Map o : currentVideoNameList) {

                    HashMap hashMap = new HashMap();

                    String _currentVideoName = o.get("file_name").toString();
                    String _formcontentname = o.get("formcontentname").toString();
                    String time = o.get("time").toString();

                    String url = subtaskid + File.separator + globle_edit_geoid + File.separator + _currentVideoName;
                    hashMap.put("relate_url", url);
                    hashMap.put("file_name", _currentVideoName);
                    hashMap.put("formcontentname", _formcontentname);
                    hashMap.put("time", time);
                    list.add(hashMap);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Map createGeomPG(String subtaskid, String polygon, String taskid) {

        Map returnMap = new HashMap();

        String sql = "SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '" + taskid + "')";
        Map map = toolsResposity.exeSql(sql);
        String layername = map.get("layername").toString();


        polygon = "MULTIPOLYGON(((" + polygon + ")))";
        String geom_text = "st_geomfromtext('" + polygon + "'" + ",4490)";


        //判断新增的polygon是否在 指定的范围内，如果在则插入数据库，并给出提示，如果不在返回相关信息
        String sql_polygon = "SELECT polygon FROM public.t_subtask_polygon where subtaskid = '" + subtaskid + "'";
        Map map_polygon = toolsResposity.exeSql(sql_polygon);
        String multiPolygonExtent_str = map_polygon.get("polygon").toString();

        boolean isIn = true;
        try {
//            GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);
            GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);
            WKTReader reader = new WKTReader(geometryFactory);
            MultiPolygon multiPolygonExtent = (MultiPolygon) reader.read(multiPolygonExtent_str);
            MultiPolygon multiPolygonInput = (MultiPolygon) reader.read(polygon);
            if (multiPolygonExtent.contains(multiPolygonInput)) {
                isIn = true;
            } else {
                isIn = false;
            }
        } catch (Exception e) {
            isIn = false;
        }


        if (isIn) {
            String sql_insert_geom = "INSERT INTO public.t_subtask_geom(subtaskid,layername,iscreated,geom,taskid) VALUES (" + subtaskid + ",'" + layername + "'," + true + "," + geom_text + ",'" + taskid + "');";
            toolsResposity.exeSql(sql_insert_geom);
            returnMap.put("success", true);
        } else {

            returnMap.put("success", false);
            returnMap.put("msg", "新绘制的图斑，不在作业区！");
        }

        return returnMap;
    }

    @Override
    public void createGeomMongodb(String subtaskid, String polygon, String taskid) {
        String sql = "SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '" + taskid + "')";
        Map map = toolsResposity.exeSql(sql);
        String layername = map.get("layername").toString();

        GeomEntity geomEntity = new GeomEntity();
        geomEntity.setCreated(true);
        geomEntity.setSubtaskid(Integer.parseInt(subtaskid));
        geomEntity.setLayername(layername);
        Map geomMap = new HashMap();

        geomMap.put("type", "new");
        geomMap.put("gid", subtaskid + "_" + System.currentTimeMillis());
        geomMap.put("polygon", polygon);
        geomEntity.setNewGeom(geomMap);
        mongoTemplate.save(geomEntity);
    }

    @Override
    public void updateGeomMongoDB(String gid, String polygon) {
        Query query = new Query();
        query.addCriteria(Criteria.where("newGeom.gid").is(gid));

        Update update = new Update();
        update.set("newGeom.polygon", polygon);
        mongoTemplate.upsert(query, update, "geomEntity");
    }

    @Override
    public Map updateGeomPG(String subtaskid, String edit_geoid, String polygon) {
        Map returnMap = new HashMap();
        //判断新增的polygon是否在 指定的范围内，如果在则插入数据库，并给出提示，如果不在返回相关信息
        String sql_polygon = "SELECT polygon FROM public.t_subtask_polygon where subtaskid = '" + subtaskid + "'";
        Map map_polygon = toolsResposity.exeSql(sql_polygon);
        String multiPolygonExtent_str = map_polygon.get("polygon").toString();

        polygon = "MULTIPOLYGON(((" + polygon + ")))";
        boolean isIn = true;
        try {
//            GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);
            GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory();
            WKTReader reader = new WKTReader(geometryFactory);
            MultiPolygon multiPolygonExtent = (MultiPolygon) reader.read(multiPolygonExtent_str);
            MultiPolygon multiPolygonInput = (MultiPolygon) reader.read(polygon);
            if (multiPolygonExtent.contains(multiPolygonInput)) {
                isIn = true;
            } else {
                isIn = false;
            }
        } catch (Exception e) {
            isIn = false;
            returnMap.put("success", false);
            returnMap.put("msg", e.toString());
            return returnMap;
        }

        if (isIn) {

            String geom_text = "st_geomfromtext('" + polygon + "'" + ",4490)";
            String sql_update = "UPDATE public.t_subtask_geom SET  geom=" + geom_text + " WHERE id= " + edit_geoid;
            toolsResposity.exeSql(sql_update);
            returnMap.put("success", true);
        } else {

            returnMap.put("success", false);
            returnMap.put("msg", "新绘制的图斑，不在作业区！");
        }

        return returnMap;


    }

    /**
     * @return java.util.List
     * @Author zxw
     * @Description //TODO  修改 原来queryTableFile接口，原service层未动
     * @Date 2023/10/24  14:03   [subtaskid, edit_geoid]
     **/
    @Override
    public List queryTableFile(String subtaskid, String edit_geoid) {
        List returnList = new ArrayList();

        String base64 = null;
        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
        String s = "";
        try {
            //根据id查询文件metadata.globle_gid
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid);
            criteria.and("metadata.globle_userid").is(userid + "");
            Query query = new Query(criteria);
            GridFSFindIterable gridFSFiles = gridFsTemplate.find(query);

            for (GridFSFile gridFSFile : gridFSFiles) {
                Map returnMap = new HashMap();
                Document metadataDocument = gridFSFile.getMetadata();

                String formcontentname = metadataDocument.get("globle_formcontentname").toString();
                String filename = metadataDocument.get("filename").toString();


                String lon = metadataDocument.get("lon").toString();
                String lat = metadataDocument.get("lat").toString();

                String alpha = metadataDocument.get("alpha").toString();
                String beta = metadataDocument.get("beta").toString();
                String gamma = metadataDocument.get("gamma").toString();
                String time = metadataDocument.get("time").toString();

                MongoDatabase mongoDatabase = mongoTemplate.getDb();
                GridFSBucket gridFSBucket = GridFSBuckets.create(mongoDatabase);
                //打开流下载对象
                GridFSDownloadStream downloadStream =
                        gridFSBucket.openDownloadStream(gridFSFile.getObjectId());
                //获取流对象
                GridFsResource gridFsResource = new GridFsResource(gridFSFile, downloadStream);

                //添加日期目录
                String format = sdf.format(new Date());
                // 使用时间戳确保唯一
                String uniqueFolderName = format + "_" + System.currentTimeMillis();
                File fileDir = new File(tempJpgFile + uniqueFolderName);
                //指定本地文件夹存储图片
                String filePath = fileDir.getAbsolutePath() + File.separator + "temp2.jpg";

                File targetFile = new File(filePath);
                FileUtils.copyInputStreamToFile(gridFsResource.getInputStream(), targetFile);

                try {
                    base64 = convertFileToBase64(filePath);
                    returnMap.put("image", base64);
                    returnMap.put("formcontentname", formcontentname);
                    returnMap.put("filename", filename);
                    returnMap.put("alpha", alpha);
                    returnMap.put("beta", beta);
                    returnMap.put("gamma", gamma);
                    returnMap.put("lon", lon);
                    returnMap.put("lat", lat);
                    returnMap.put("time", time);
                    returnList.add(returnMap);

                    //用这个强制删除  zxw
                    FileUtils.deleteDirectory(fileDir);

                } catch (Exception e) {
                    e.printStackTrace();
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnList;
    }


    /**
     * @return java.util.List
     * @Author zxw
     * @Description //TODO  查找表 采集员登录  修改 原来queryTableFile接口，原service层未动
     * @Date 2023/10/24  14:03   [subtaskid, edit_geoid]
     **/
    @Override
    public List queryTableFileByCjy(String subtaskid, String edit_geoid) {
        List returnList = new ArrayList();

        String base64 = null;

        String s = "";
        try {
            //根据id查询文件metadata.globle_gid
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid);

            Query query = new Query(criteria);
            GridFSFindIterable gridFSFiles = gridFsTemplate.find(query);

            for (GridFSFile gridFSFile : gridFSFiles) {
                Map returnMap = new HashMap();
                Document metadataDocument = gridFSFile.getMetadata();

                String formcontentname = metadataDocument.get("globle_formcontentname").toString();
                String filename = metadataDocument.get("filename").toString();


                String lon = metadataDocument.get("lon").toString();
                String lat = metadataDocument.get("lat").toString();

                String alpha = metadataDocument.get("alpha").toString();
                String beta = metadataDocument.get("beta").toString();
                String gamma = metadataDocument.get("gamma").toString();

                MongoDatabase mongoDatabase = mongoTemplate.getDb();
                GridFSBucket gridFSBucket = GridFSBuckets.create(mongoDatabase);
                //打开流下载对象
                GridFSDownloadStream downloadStream =
                        gridFSBucket.openDownloadStream(gridFSFile.getObjectId());
                //获取流对象
                GridFsResource gridFsResource = new GridFsResource(gridFSFile, downloadStream);

                //添加日期目录
                String format = sdf.format(new Date());
                // 使用时间戳确保唯一
                String uniqueFolderName = format + "_" + System.currentTimeMillis();
                File fileDir = new File(tempJpgFile + uniqueFolderName);
                //指定本地文件夹存储图片
                String filePath = fileDir.getAbsolutePath() + File.separator + "tempCjy.jpg";

                File targetFile = new File(filePath);
                FileUtils.copyInputStreamToFile(gridFsResource.getInputStream(), targetFile);

                try {
                    base64 = convertFileToBase64(filePath);
                    returnMap.put("image", base64);
                    returnMap.put("formcontentname", formcontentname);
                    returnMap.put("filename", filename);
                    returnMap.put("alpha", alpha);
                    returnMap.put("beta", beta);
                    returnMap.put("gamma", gamma);
                    returnMap.put("lon", lon);
                    returnMap.put("lat", lat);
                    returnList.add(returnMap);

                    //用这个强制删除
                    FileUtils.deleteDirectory(fileDir);

                } catch (Exception e) {
                    e.printStackTrace();
                }
                System.out.println("打印文件:" + s);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnList;
    }


    @Override
    public void deleteTableFile(String subtaskid, String filename) {
        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.filename").is(filename);
        Query query = new Query(criteria);
        gridFsTemplate.delete(query);
    }

    @Override
    public Map deleteGeomById(String edit_geoid, String subtaskid) {

        Map returnMap = new HashMap();

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.globle_edit_geoid").is(edit_geoid + "");
        criteria.and("metadata.globle_userid").is(Integer.parseInt(userid + ""));
        Query query1 = new Query(criteria);
        JSONObject jsonObject = mongoTemplate.findOne(query1, JSONObject.class, "tableEntity");
        if (jsonObject != null) {
            //不能删除
            returnMap.put("canDelete", false);
            returnMap.put("msg", "存在表单使用该图斑，无法删除！");
        } else {
            //可以删除
            returnMap.put("canDelete", true);
            String sql_delete = "delete from t_subtask_geom where id = " + edit_geoid;
            toolsResposity.exeSql(sql_delete);
        }
        return returnMap;
    }

    @Override
    public Map deleteGeomAndTableById(String edit_geoid, String subtaskid) {
        Map returnMap = new HashMap();

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
        //删除表单
        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.globle_edit_geoid").is(edit_geoid + "");
        criteria.and("metadata.globle_userid").is(Integer.parseInt(userid + ""));
        Query query1 = new Query(criteria);
        mongoTemplate.findAndRemove(query1, JSONObject.class, "tableEntity");

        //删除图斑
        String sql_delete = "delete from t_subtask_geom where id = " + edit_geoid;
        toolsResposity.exeSql(sql_delete);

        //删除文件
        Criteria criteriaFile = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteriaFile.and("metadata.globle_edit_geoid").is(edit_geoid);
        Query query = new Query(criteriaFile);
        gridFsTemplate.delete(query);

        return returnMap;
    }

    /**
     * 本地文件（图片、excel等）转换成Base64字符串
     *
     * @param imgPath
     */
    public String convertFileToBase64(String imgPath) {
        byte[] data = null;
        // 读取图片字节数组
        try {
            InputStream in = new FileInputStream(imgPath);
            System.out.println("照片文件大小（字节）=" + in.available());
            data = new byte[in.available()];
            in.read(data);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 对字节数组进行Base64编码，得到Base64编码的字符串
//        BASE64Encoder encoder = new BASE64Encoder();
//        String base64Str = encoder.encode(data);
        return Base64.encodeBase64String(data);
    }

    @Override
    public Map getSubTasksPG(String taskid, String caseid) throws IOException, ParseException {

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();
//3129
        //select layerid from t_case_layer where caseid in (select caseid from t_task where id = sss)
        Map returnMap = new HashMap();
        List subList = new ArrayList();
        String sql = "select * from t_subtask where taskid in (" + taskid + ") and userid = " + userid;
        List subtaskList = toolsResposity.exeSqlList(sql);
        for (int i = 0; i < subtaskList.size(); i++) {
            Map subTaskMap = (Map) subtaskList.get(i);
            String subtaskid = subTaskMap.get("id").toString();
            //查询范围pg
//            String polygonSql = "SELECT *,st_astext(geom) as wkt FROM public.t_subtask_polygon where subtaskid = '" + subtaskid + "'";
            //该面向对象要面向对象
            List<SubtaskPolygon> listExtent = toolsResposity.querySubtaskPolygonBYSubtaskId(Integer.parseInt(subtaskid));
            for (SubtaskPolygon subtaskPolygon1 : listExtent) {
                String geoJSON = convertWKTtoGeoJSON(subtaskPolygon1.getWkt());
                subtaskPolygon1.setWkt(geoJSON);
            }
            //废弃这个方法：filterList   麻烦又出错    20240416日改的
//            subTaskMap.put("extent", filterList(listExtent));
            subTaskMap.put("extent", listExtent);
            //查询相交图斑mongodb
            String subtask_geom_sql = "SELECT *,st_astext(geom) FROM public.t_subtask_geom where subtaskid = '" + subtaskid + "'";
            List listGeom = toolsResposity.exeSqlList(subtask_geom_sql);
            List listGeomAndTable = new ArrayList();
            List listNewGeomAndTable = new ArrayList();//新的图斑
            for (int j = 0; j < listGeom.size(); j++) {
                Map geomEntity = (Map) listGeom.get(j);
                String edit_geoid = geomEntity.get("id").toString();
                boolean isCreated = (boolean) geomEntity.get("iscreated");
                //原来就有的图斑
                Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
                criteria.and("metadata.globle_edit_geoid").is(edit_geoid + "");
                criteria.and("metadata.globle_userid").is(Integer.parseInt(userid + ""));
                Query query1 = new Query(criteria);
                JSONObject jsonObject = mongoTemplate.findOne(query1, JSONObject.class, "tableEntity");
                if (jsonObject != null) {
                    System.out.println(jsonObject.toJSONString());
                }
                geomEntity.put("table", jsonObject);
                listGeomAndTable.add(geomEntity);
            }
            subTaskMap.put("editPolygon", transToWkt(listGeomAndTable));
            //subTaskMap.put("newPolygon",listNewGeomAndTable);
            subList.add(subTaskMap);
        }

        //baselayer
        String layerBaseSql = "select * from t_layer where id in (select layerid from t_case_baselayer where caseid = '" + caseid + "')";
        List layerBaseList = toolsResposity.exeSqlList(layerBaseSql);
        //speciallayer
        String layerSpecialSql = "select * from t_layer where id in (select layerid from t_case_speciallayer where caseid = '" + caseid + "')";
        List layerSpecialList = toolsResposity.exeSqlList(layerSpecialSql);
        //editlayer
        String layerEditSql = "select * from t_layer where id in (select layerid from t_case_editlayer where caseid = '" + caseid + "')";
        List layerEditList = toolsResposity.exeSqlList(layerEditSql);

        returnMap.put("subtask", subList);
        returnMap.put("baselayers", layerBaseList);
        returnMap.put("speciallayers", layerSpecialList);
        returnMap.put("editlayers", layerEditList);
        return returnMap;
    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO 修改bug 导入shp数据存在主键可能是id  还可能是gid问题
     * @Date 2023/12/6  10:21
     **/
    @Override
    public Map getDetail(String edit_geoid) {
        String sql_search = "select * from t_subtask_geom where id = '" + edit_geoid + "'";
        Map geomEntity = toolsResposity.exeSql(sql_search);
        String gid = geomEntity.get("gid").toString();
        String layername = geomEntity.get("layername").toString();

        String sql;
        Map orgTable = new HashMap();
        String primaryKey = "gid"; // 主键字段名称
        try {
            sql = String.format("select * from %s  where %s = '%s'", layername, primaryKey, gid);
            orgTable = toolsResposity.exeSql(sql);
        } catch (Exception e) {
            primaryKey = "id"; // 如果出现异常，则使用另一个主键字段
            sql = String.format("select * from %s  where %s = '%s'", layername, primaryKey, gid);
            orgTable = toolsResposity.exeSql(sql);
        }

        return orgTable;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //  原有接口 依赖GDAL 改geotools形式  不依赖于环境
     * @Date 13:26 2024/2/22
     **/
    @Override
    public void exportGPSShp(HttpServletRequest request, HttpServletResponse response, String subtaskid, String date_start, String date_end) {
        String sql = "select * from t_gps t where subtaskid = " + subtaskid + " and (create_time between '" + date_start + "' and '" + date_end + "')";
//        String sql = "select * from t_gps t where subtaskid = " + subtaskid by creat_time  asc;
        List list = toolsResposity.exeSqlList(sql);
        Long startTs = System.currentTimeMillis();
        String shpPointName = startTs + "point.shp";
        String shpLineName = startTs + "line.shp";
        String shpFile = shpUpdate;
        File fileFolder = new File(shpFile);
        if (!fileFolder.isDirectory()) {
            fileFolder.mkdirs();
        }
        ShpWriter.createPointShp(list, shpFile + shpPointName);
        ShpWriter.createLineShp(list, shpFile + shpLineName);
        //压缩
        try {
            //遍历shp 压缩
            File file = new File(shpFile);        //获取其file对象
            File[] fs = file.listFiles();    //遍历path下的文件和目录，放在File数组中
            List<String> sourceFilePaths = new ArrayList<String>();
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {
                    sourceFilePaths.add(f.getAbsolutePath());
                }
            }
            String zipName = startTs + ".zip";
            String zipTempFilePath = shpFile + zipName;
            //调用压缩
            int s = Zip.compress(sourceFilePaths, zipTempFilePath, false);
            System.out.println("成功压缩" + s + "个文件");

            //上传
            File upFile = new File(zipTempFilePath);
            FileUtil.downloadFileAndDelete(request, response, upFile, true);

            //删除shp数据
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {

                    File tempFile = new File(f.getAbsolutePath());
                    tempFile.delete();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //  原有接口 依赖GDAL 改geotools形式  不依赖于环境
     * @Date 13:26 2024/2/22
     **/
    @Override
    public void exportGPSShp(HttpServletRequest request, HttpServletResponse response, String subtaskid) {
        String sql = "select * from t_gps t where subtaskid = " + subtaskid;
//        String sql = "select * from t_gps t where subtaskid = " + subtaskid by creat_time  asc;
        List list = toolsResposity.exeSqlList(sql);
        Long startTs = System.currentTimeMillis();
        String shpPointName = startTs + "point.shp";
        String shpLineName = startTs + "line.shp";
        String shpFile = shpUpdate;
        File fileFolder = new File(shpFile);
        if (!fileFolder.isDirectory()) {
            fileFolder.mkdirs();
        }
        ShpWriter.createPointShp(list, shpFile + shpPointName);
        ShpWriter.createLineShp(list, shpFile + shpLineName);
        //压缩
        try {
            //遍历shp 压缩
            File file = new File(shpFile);        //获取其file对象
            File[] fs = file.listFiles();    //遍历path下的文件和目录，放在File数组中
            List<String> sourceFilePaths = new ArrayList<String>();
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {
                    sourceFilePaths.add(f.getAbsolutePath());
                }
            }
            String zipName = startTs + ".zip";
            String zipTempFilePath = shpFile + zipName;
            //调用压缩
            int s = Zip.compress(sourceFilePaths, zipTempFilePath, false);
            System.out.println("成功压缩" + s + "个文件");

            //上传
            File upFile = new File(zipTempFilePath);
            FileUtil.downloadFileAndDelete(request, response, upFile, true);

            //删除shp数据
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {

                    File tempFile = new File(f.getAbsolutePath());
                    tempFile.delete();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addGPS(String nickname, String create_time, String userid, String username, String taskid, String taskname, String subtaskid, String lat, String lon) {
        if (null != lat && StringUtils.isNotBlank(lat) || null != lon && StringUtils.isNotBlank(lon)) {
            String sql_insert_geom = "INSERT INTO public.t_gps(nickname,create_time,userid,username,taskid,taskname,subtaskid,lat,lon) VALUES ('" + nickname + "','" + create_time + "'," + userid + ",'" + username + "'," + taskid + ",'" + taskname + "'," + subtaskid + ",'" + lat + "','" + lon + "');";
            toolsResposity.exeSql(sql_insert_geom);
        }

    }

    @Override
    public Map getTimeSpan(String subtaskid) {
        Map map = new HashMap();
        String sql_search1 = "SELECT min(create_time) FROM public.t_gps where subtaskid = " + subtaskid;
        Map map1 = toolsResposity.exeSql(sql_search1);

        String sql_search2 = "SELECT max(create_time) FROM public.t_gps where subtaskid = " + subtaskid;
        Map map2 = toolsResposity.exeSql(sql_search2);

        map.put("min", map1.get("min").toString());
        map.put("max", map2.get("max").toString());
        return map;
    }

    @Override
    public List getGPSByTime(String subtaskid, String minTime, String maxTime) {
        String sql_search = "SELECT * FROM public.t_gps where subtaskid = " + subtaskid + " and create_time >= '" + minTime + "' and create_time <= '" + maxTime + "'";
        List list = toolsResposity.exeSqlList(sql_search);
        return list;
    }

    @Override
    public void syncData(String taskid, String caseid) {
        //-1、查询编辑的图层
        //0、根据caseid查找表单结构
        //1、查询t_subtask_geom表中的数据，找到gid。2、根据gid查找获取id值，去mongodb中查找

        //-1
        String sql_case_editlayer = "SELECT * FROM public.t_case_editlayer where caseid = '" + caseid + "'";
        Map case_editlayerMap = toolsResposity.exeSql(sql_case_editlayer);
        String layername = case_editlayerMap.get("layername").toString();
        //0
        String sql_search = "SELECT * FROM public.t_form_content where formid in (SELECT formid FROM public.t_case_form where caseid = '" + caseid + "')";
        List form_content_list = toolsResposity.exeSqlList(sql_search);

        //1
        String sql_geom_search = "SELECT id,gid FROM public.t_subtask_geom where taskid = '" + taskid + "'";
        List id_list = toolsResposity.exeSqlList(sql_geom_search);

        for (int i = 0; i < form_content_list.size(); i++) {
            Map map = (Map) form_content_list.get(i);
            String formcontentname = map.get("formcontentname").toString();
            String type = map.get("type").toString();

            if ("图片_普通".equals(type)) {
                //是图片

                String s = "";
                for (int j = 0; j < id_list.size(); j++) {
                    Map mapIds = (Map) id_list.get(j);
                    String id = mapIds.get("id").toString();//mongodb中的globle_edit_geoid
                    String gid = mapIds.get("gid").toString();//t_subtask_geom中的gid也是sick_tree中的id

                    try {
                        //根据id查询文件metadata.globle_gid
                        Criteria criteria = Criteria.where("metadata.globle_edit_geoid").is(id);
                        Query query = new Query(criteria);
                        GridFSFindIterable gridFSFiles = gridFsTemplate.find(query);


                        for (GridFSFile gridFSFile : gridFSFiles) {
                            Map returnMap = new HashMap();
                            Document metadataDocument = gridFSFile.getMetadata();

                            String formcontentname_ = metadataDocument.get("globle_formcontentname").toString();
                            String filename = metadataDocument.get("filename").toString();

                            MongoDatabase mongoDatabase = mongoTemplate.getDb();
                            GridFSBucket gridFSBucket = GridFSBuckets.create(mongoDatabase);
                            //打开流下载对象
                            GridFSDownloadStream downloadStream =
                                    gridFSBucket.openDownloadStream(gridFSFile.getObjectId());
                            //获取流对象
                            GridFsResource gridFsResource = new GridFsResource(gridFSFile, downloadStream);

                            //添加日期目录
                            String format = sdf.format(new Date());
                            // 使用时间戳确保唯一
                            String uniqueFolderName = format + "_" + System.currentTimeMillis();
                            File fileDir = new File(localStoreJpgFile + uniqueFolderName);
                            //指定本地文件夹存储图片
                            String filePath = fileDir.getAbsolutePath() + File.separator + "temp2.jpg";

                            File targetFile = new File(filePath);
                            FileUtils.copyInputStreamToFile(gridFsResource.getInputStream(), targetFile);

                            try {
                                FileInputStream in = null;
                                in = ImageUtil.readImage(filePath);
                                String sql_update = "UPDATE " + layername + " SET  " + formcontentname_ + "='" + in + "' WHERE id= '" + gid + "'";
                                toolsResposity.exeSql(sql_update);
                                targetFile.delete();
                                fileDir.delete();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            System.out.println("打印文件:" + s);
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

            } else {

                String value = "";
                for (int j = 0; j < id_list.size(); j++) {
                    Map mapIds = (Map) id_list.get(j);
                    String id = mapIds.get("id").toString();//mongodb中的globle_edit_geoid
                    String gid = mapIds.get("gid").toString();//t_subtask_geom中的gid也是sick_tree中的id
                    //去mongodb中查找
                    Criteria criteria = Criteria.where("metadata.globle_edit_geoid").is(id);
                    Query query = new Query(criteria);
                    Map contentMap = mongoTemplate.findOne(query, JSONObject.class, "tableEntity");
                    if (contentMap != null) {
                        value = contentMap.get(formcontentname).toString();
                        //插入t_subtask_geom表数据
                        String sql_update = "UPDATE " + layername + " SET  " + formcontentname + "='" + value + "' WHERE id= '" + gid + "'";
                        toolsResposity.exeSql(sql_update);
                    }
                }
            }

        }
    }

    @Override
    public Map queryTables(String subtaskid, String caseid) {

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

        Map returnMap = new HashMap();
        //结构
        String sql = "select * from t_form_content where formid in (SELECT formid FROM public.t_case_form where caseid = '" + caseid + "')";
        List structList = toolsResposity.exeSqlList(sql);
        returnMap.put("struct", structList);

        //内容

        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.globle_userid").is(userid);
        Query query = new Query(criteria);
        List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");
        if (contentList.size() == 0) {
            returnMap.put("hasContent", false);
        } else {
            returnMap.put("hasContent", true);
        }
        returnMap.put("content", contentList);

        return returnMap;
    }


    @Override
    public Map getSubTasksMongoDb(String taskid, String caseid) {

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

        //select layerid from t_case_layer where caseid in (select caseid from t_task where id = sss)
        Map returnMap = new HashMap();
        List subList = new ArrayList();
        String sql = "select * from t_subtask where taskid in (" + taskid + ") and userid = " + userid;
        List subtaskList = toolsResposity.exeSqlList(sql);
        for (int i = 0; i < subtaskList.size(); i++) {
            Map subTaskMap = (Map) subtaskList.get(i);
            String subtaskid = subTaskMap.get("id").toString();
            //查询范围pg
            String polygonSql = "SELECT * FROM public.t_subtask_polygon where subtaskid = '" + subtaskid + "'";
            List listExtent = toolsResposity.exeSqlList(polygonSql);
            subTaskMap.put("extent", listExtent);
            //查询相交图斑mongodb
            Query query = new Query(Criteria.where("subtaskid").is(Integer.parseInt(subtaskid)));
            List listGeom = mongoTemplate.find(query, GeomEntity.class);
            List listGeomAndTable = new ArrayList();
            List listNewGeomAndTable = new ArrayList();//新的图斑
            for (int j = 0; j < listGeom.size(); j++) {
                GeomEntity geomEntity = (GeomEntity) listGeom.get(j);

                boolean isCreated = geomEntity.isCreated();
                //原来就有的图斑
                if (!isCreated) {
                    int gid = geomEntity.getGid();
                    Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
                    criteria.and("metadata.globle_gid").is(gid + "");
                    criteria.and("metadata.globle_userid").is(Integer.parseInt(userid + ""));
                    Query query1 = new Query(criteria);
                    JSONObject jsonObject = mongoTemplate.findOne(query1, JSONObject.class, "tableEntity");
                    if (jsonObject != null) {
                        System.out.println(jsonObject.toJSONString());
                    }
                    geomEntity.setTable(jsonObject);
                    listGeomAndTable.add(geomEntity);
                } else {
                    Map newGeomMap = geomEntity.getNewGeom();
                    String polygon = newGeomMap.get("polygon").toString();
                    String gid = newGeomMap.get("gid").toString();
                    String type = newGeomMap.get("type").toString();
                    Map tempMap = new HashMap();
                    tempMap.put("type", type);
                    tempMap.put("geo", polygon);
                    tempMap.put("gid", gid);
                    tempMap.put("table", geomEntity.getTable());
                    String multiStr = "POLYGON((" + polygon + "))";
                    try {
                        String sql_temp = "SELECT ST_AsText(ST_Centroid('" + multiStr + "'))";
                        Map centerMap = toolsResposity.exeSql(sql_temp);
                        String center = centerMap.get("st_astext").toString();
                        center = center.substring(6, center.length() - 1);
                        tempMap.put("center", center);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    listNewGeomAndTable.add(tempMap);
                }

            }

            subTaskMap.put("editPolygon", transToWkt(listGeomAndTable));
            subTaskMap.put("newPolygon", listNewGeomAndTable);
            subList.add(subTaskMap);
        }

        String layerSql = "select * from t_layer where id in (select layerid from t_case_layer where caseid = '" + caseid + "')";
        List layerList = toolsResposity.exeSqlList(layerSql);
        returnMap.put("subtask", subList);
        returnMap.put("layers", layerList);
        return returnMap;
    }

    public List transToWktWithField(List listGemo) {

        List returnList = new ArrayList();
        for (int i = 0; i < listGemo.size(); i++) {
            Map geomEntity = (Map) listGemo.get(i);
            String gid = geomEntity.get("gid").toString();
            String layername = geomEntity.get("layername").toString();
            String sql = "select * from " + layername + " where id = '" + gid + "'";
            Map orgTable = toolsResposity.exeSql(sql);

            String st_astext = geomEntity.get("st_astext").toString();
            boolean iscreated = (boolean) geomEntity.get("iscreated");
            String edit_geoid = geomEntity.get("id").toString();
            Map tableMap = (Map) geomEntity.get("table");

            Map geoMap = filterGeo(edit_geoid, iscreated, st_astext);
            geoMap.put("table", tableMap);
            geoMap.put("orgTable", orgTable);
            returnList.add(geoMap);
        }
        return returnList;
    }

    public List transToWkt(List listGemo) {

        List returnList = new ArrayList();
        for (int i = 0; i < listGemo.size(); i++) {
            Map geomEntity = (Map) listGemo.get(i);

            String st_astext = geomEntity.get("st_astext").toString();
            boolean iscreated = (boolean) geomEntity.get("iscreated");
            String edit_geoid = geomEntity.get("id").toString();
            Map tableMap = (Map) geomEntity.get("table");

            Map geoMap = filterGeo(edit_geoid, iscreated, st_astext);
            geoMap.put("table", tableMap);
            returnList.add(geoMap);
        }
        return returnList;
    }

    public List transToWktMongoDb(List listGemo) {

        List returnList = new ArrayList();
        for (int i = 0; i < listGemo.size(); i++) {
            GeomEntity geomEntity = (GeomEntity) listGemo.get(i);
            JSONObject jsonObject = geomEntity.getTable();
            String geojson = geomEntity.getGeojson();

            cn.hutool.json.JSONObject json = (cn.hutool.json.JSONObject) JSONUtil.parseObj(geojson);
            String gid = json.get("gid").toString();
            json.remove("gid");
            cn.hutool.json.JSONObject geometryJson = (cn.hutool.json.JSONObject) json.get("geometry");
            geometryJson.remove("crs");
            System.out.println("WKT 转 Feature：" + GeoTools.geoJsonToWkt(json));

            Map geoMap = filterGeo(null, false, GeoTools.geoJsonToWkt(json));
            geoMap.put("table", jsonObject);
            returnList.add(geoMap);
        }
        return returnList;
    }

    /**
     * 过滤geom字段
     *
     * @param
     * @return
     */
    private Map filterGeo(String edit_geoid, boolean iscreated, String st_astext) {

        String multiStr = st_astext;

        Map map = new HashMap();
        List listPolygon = new ArrayList();

        if (st_astext.contains("MULTIPOLYGON")) {
            st_astext = st_astext.substring(15, st_astext.length() - 3);

            String[] polygons = st_astext.split("\\)\\),\\(\\(");

            for (int j = 0; j < polygons.length; j++) {
                String polygon = polygons[j].trim();
                if (polygon.contains("),(")) {
                    String[] polygonHole = polygon.split("\\),\\(");
                    listPolygon.add(polygonHole);
                } else {
                    listPolygon.add(polygon);
                }
            }
            map.put("iscreated", iscreated);
            map.put("edit_geoid", edit_geoid);
            map.put("geo", listPolygon);
            map.put("geotype", "polygon");

            try {
                String sql = "SELECT ST_AsText(ST_Centroid('" + multiStr + "'))";
                Map centerMap = toolsResposity.exeSql(sql);
                String center = centerMap.get("st_astext").toString();
                center = center.substring(6, center.length() - 1);
                map.put("center", center);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else if (st_astext.contains("POLYGON")) {
            st_astext = st_astext.substring(9, st_astext.length() - 2);
            map.put("iscreated", iscreated);
            map.put("edit_geoid", edit_geoid);
            map.put("geo", st_astext);
            map.put("geotype", "polygon");
        } else {
            st_astext = st_astext.substring(6, st_astext.length() - 2);

            map.put("iscreated", iscreated);
            map.put("edit_geoid", edit_geoid);
            map.put("geo", st_astext);
            map.put("geotype", "point");
        }


        return map;
    }


    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO 根据filename更改照片的朝向，这里filename是metadata中的filename
     * @Date 13:21 2024/1/10
     **/
    @Override
    public void updatePhotoInMongoDb(String filename, String alpha, String beta, String gamma, String lat, String lon) {

        Criteria criteria = Criteria.where("metadata.filename").is(filename);
        Query query = new Query(criteria);
        JSONObject one = mongoTemplate.findOne(query, JSONObject.class, "fs.files");
        Set<String> Strings = one.keySet();
        for (String string : Strings) {
            if (string.equalsIgnoreCase("metadata")) {
                Map metadata = (Map) one.get("metadata");
                metadata.put("alpha", alpha);
                metadata.put("beta", beta);
                metadata.put("lat", lat);
                metadata.put("gamma", gamma);
                metadata.put("lon", lon);
                one.put("metadata", metadata);
                mongoTemplate.save(one, "fs.files");
            }
        }

    }


    //查询指定任务中所有已核查的图斑(globle_status字段为已提交) 传入参数	{taskid:97}
    @Override
    public List getCheckedPolygonByTaskId(Integer taskid) {

        List<Map> resultList = new LinkedList<>();

        String querySubtaskSql = String.format("SELECT id, subtaskid, gid, layername, iscreated, geom, taskid " +
                "FROM t_subtask_geom where taskid=%d", taskid);

        List<Map> list = toolsResposity.exeSqlList(querySubtaskSql);

        for (Map map : list) {//循环该任务下的所有子任务

            Integer subtaskid = (Integer) map.get("subtaskid");
            Long edit_geoid = (Long) map.get("id");

            //子任务已经核实的有多少、统计
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid + "");
//            criteria.and("metadata.globle_status").is("已核实").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            criteria.and("metadata.globle_status").is("已提交").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            Query query = new Query(criteria);
            List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");

            System.out.println(contentList);
            if (!contentList.isEmpty()) {//已经核查的图斑

                List photoInfoList = queryTableFileByGly(subtaskid.toString(), edit_geoid.toString());

                String caseId = String.format("SELECT caseid  FROM  t_task where id=%d", taskid);
                List<Map> listCaseId = toolsResposity.exeSqlList(caseId);

                List tableContentList = new ArrayList();//表单内容
                for (Map map1 : listCaseId) {
                    Integer caseid = (Integer) map1.get("caseid");
                    Map mapResult = queryTablesByGly(subtaskid.toString(), caseid.toString(), edit_geoid + "");
                    tableContentList.add(mapResult);
                }


                //改子任务已核实
                String sql = String.format("SELECT id, subtaskid, taskid,st_astext(ST_Transform(t.geom,4326))  from t_subtask_geom t where subtaskid=%d and id=%d and taskid=%d", subtaskid, edit_geoid, taskid);
                List<Map> listGeom = toolsResposity.exeSqlList(sql);//

                for (Map geoMap : listGeom) {
                    String geomStr = geoMap.get("st_astext").toString();
                    String geoStrResult = "";

                    if (geomStr.contains("MULTIPOLYGON")) {
                        geoStrResult = geomStr.replace("MULTIPOLYGON(((", "")
                                .replace(" )))", "").replace(")))", "");
                    } else if (geomStr.contains("POLYGON")) {
                        geoStrResult = geomStr.replace("POLYGON((", "")
                                .replace(" ))", "").replace("))", "");

                    }
                    geoMap.put("geo", geoStrResult);
                }

                //添加视频
                List listVideos = queryVideoFile(subtaskid + "", edit_geoid + "");
                String subTaskVerifyTime = toolsResposity.getVerifyTime(subtaskid);
                Map tempMap = new HashMap();
                tempMap.put("tableContent", tableContentList);
                tempMap.put("photos", photoInfoList);
                tempMap.put("videos", listVideos);
                tempMap.put("geom", listGeom);
                tempMap.put("verify_time", subTaskVerifyTime);

                resultList.add(tempMap);
            }
        }

        return resultList;

    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO  按照编号查询已经完成的任务
     * @Date 15:19 2024/1/10
     **/
    @Override
    public List queryById(String id, String taskid) {
        String querySubtaskSql = "";
        if (null == id && id.equals("")) {
            querySubtaskSql = String.format("SELECT id, subtaskid, gid, layername, iscreated, geom, taskid FROM t_subtask_geom where taskid=%d", Integer.parseInt(taskid));
        } else {
            querySubtaskSql = String.format("SELECT id, subtaskid, gid, layername, iscreated, geom, taskid " +
                    "FROM t_subtask_geom WHERE taskid=%d AND id::TEXT LIKE '%%%s%%'", Integer.parseInt(taskid), Integer.parseInt(id));
        }

        List<Map> resultList = new LinkedList<>();


        List<Map> list = toolsResposity.exeSqlList(querySubtaskSql);
        //循环该任务下的所有子任务
        for (Map map : list) {

            Integer subtaskid = (Integer) map.get("subtaskid");
            Long edit_geoid = (Long) map.get("id");

            //子任务已经核实的有多少、统计
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid + "");
//            criteria.and("metadata.globle_status").is("已核实").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            criteria.and("metadata.globle_status").is("已提交").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            Query query = new Query(criteria);
            List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");

            System.out.println(contentList);
            if (contentList.size() > 0) {//已经核查的图斑

                List photoInfoList = queryTableFileByGly(subtaskid.toString(), edit_geoid.toString());

                String caseId = String.format("SELECT caseid  FROM  t_task where id=%d", Integer.parseInt(taskid));
                List<Map> listCaseId = toolsResposity.exeSqlList(caseId);

                List tableContentList = new ArrayList();//表单内容
                for (Map map1 : listCaseId) {
                    Integer caseid = (Integer) map1.get("caseid");
                    Map mapResult = queryTablesByGly(subtaskid.toString(), caseid.toString(), edit_geoid + "");
                    tableContentList.add(mapResult);
                }

                //改子任务已核实
                String sql = String.format("SELECT id, subtaskid, taskid,st_astext(ST_Transform(t.geom,4326))  from t_subtask_geom t where subtaskid=%d and id=%d and taskid=%d", subtaskid, edit_geoid, Integer.parseInt(taskid));
                List<Map> listGeom = toolsResposity.exeSqlList(sql);
                for (Map geoMap : listGeom) {
                    String geomStr = geoMap.get("st_astext").toString();
                    String geoStrResult = "";

                    if (geomStr.contains("MULTIPOLYGON")) {
                        geoStrResult = geomStr.replace("MULTIPOLYGON(((", "")
                                .replace(" )))", "").replace(")))", "");
                    } else if (geomStr.contains("POLYGON")) {
                        geoStrResult = geomStr.replace("POLYGON((", "")
                                .replace(" ))", "").replace("))", "");

                    }
                    geoMap.put("geo", geoStrResult);
                }

                //添加视频
                List listVideos = queryVideoFile(subtaskid + "", edit_geoid + "");
                Map tempMap = new HashMap();
                tempMap.put("tableContent", tableContentList);
                tempMap.put("photos", photoInfoList);
                tempMap.put("videos", listVideos);
                tempMap.put("geom", listGeom);

                resultList.add(tempMap);
            }
        }

        return resultList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO 查询空间数据
     * @Date 13:20 2024/1/10
     **/
    @Override
    public List queryByDraw(String json) {

        List<Map> resultList = new LinkedList<>();
        List<Map> landList = new LinkedList<>();
        Map<String, List<Map>> resultMap = new HashMap<>();
        JSONObject jsonObject = JSONObject.parseObject(json);
        JSONArray addressCoordinate = jsonObject.getJSONArray("address_coordinate");
        String taskidStr = jsonObject.getString("taskid");
        Integer taskid = 0;
        if (null != taskidStr && taskidStr != null) {
            taskid = Integer.parseInt(taskidStr);
        }

        //手绘//梦然说不考虑画多个多边形的 这里就没改画多个多边形的情况
        if (addressCoordinate.size() > 1) {
            landList = toolsResposity.queryByDrawMulti(taskid, addressCoordinate.toString());
        } else {
            String addressCoordinateStr = addressCoordinate.toString().replace("[\"", "").replace("\"]", "");
            landList = toolsResposity.queryByDraw(taskid, addressCoordinateStr);
        }

        for (Map map : landList) {//循环该任务下的所有子任务

            Integer subtaskid = (Integer) map.get("subtaskid");
            Long edit_geoid = (Long) map.get("id");

            //子任务已经核实的有多少、统计
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid + "");
//            criteria.and("metadata.globle_status").is("已核实").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            criteria.and("metadata.globle_status").is("已提交").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            Query query = new Query(criteria);
            List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");

            System.out.println(contentList);
            if (contentList.size() > 0) {//已经核查的图斑

                List photoInfoList = queryTableFileByGly(subtaskid.toString(), edit_geoid.toString());

                String caseId = String.format("SELECT caseid  FROM  t_task where id=%d", taskid);
                List<Map> listCaseId = toolsResposity.exeSqlList(caseId);

                List tableContentList = new ArrayList();//表单内容
                for (Map map1 : listCaseId) {
                    Integer caseid = (Integer) map1.get("caseid");
                    Map mapResult = queryTablesByGly(subtaskid.toString(), caseid.toString(), edit_geoid + "");
                    tableContentList.add(mapResult);
                }

                //改子任务已核实
                String sql = String.format("SELECT id, subtaskid, taskid,st_astext(ST_Transform(t.geom,4326))  from t_subtask_geom t where subtaskid=%d and id=%d and taskid=%d", subtaskid, edit_geoid, taskid);
                List<Map> listGeom = toolsResposity.exeSqlList(sql);//

                for (Map geoMap : listGeom) {
                    String geomStr = geoMap.get("st_astext").toString();
                    String geoStrResult = "";

                    if (geomStr.contains("MULTIPOLYGON")) {
                        geoStrResult = geomStr.replace("MULTIPOLYGON(((", "")
                                .replace(" )))", "").replace(")))", "");
                    } else if (geomStr.contains("POLYGON")) {
                        geoStrResult = geomStr.replace("POLYGON((", "")
                                .replace(" ))", "").replace("))", "");

                    }
                    geoMap.put("geo", geoStrResult);
                }

                //添加视频
                List listVideos = queryVideoFile(subtaskid + "", edit_geoid + "");
                Map tempMap = new HashMap();
                tempMap.put("tableContent", tableContentList);
                tempMap.put("photos", photoInfoList);
                tempMap.put("videos", listVideos);
                tempMap.put("geom", listGeom);

                resultList.add(tempMap);
            }
        }

        return resultList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO 信息查询模块—按照时间查询所需要的关联查询
     * @Date 13:20 2024/1/10
     **/
    @Override
    public List queryByTimeAndTaskId(LocalDateTime startTime, LocalDateTime endTime, String taskid) {

        List<Map> resultList = new LinkedList<>();
//        List<Map> landList = queryByTime(startTime, endTime, taskid);

        String querySubtaskSql = String.format("SELECT id, subtaskid, gid, layername, iscreated, geom, taskid " +
                "FROM t_subtask_geom where taskid=%d", Integer.parseInt(taskid));

        List<Map> list = toolsResposity.exeSqlList(querySubtaskSql);

        for (Map map : list) {//循环该任务下的所有子任务

            Integer subtaskid = (Integer) map.get("subtaskid");
            Long edit_geoid = (Long) map.get("id");
            //子任务已经核实的有多少、统计
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid + "");
//            criteria.and("metadata.globle_status").is("已核实").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            criteria.and("metadata.globle_status").is("已提交").and("metadata.globle_edit_geoid").is(edit_geoid + "");
            Query query = new Query(criteria);
            List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");
//            System.out.println(contentList);
            if (contentList.size() > 0) {//已经核查的图斑
                //查找时间段内符合条件的已核查图斑
                List photoInfoList = queryTableFileByGlyAndTime(startTime, endTime, subtaskid.toString(), edit_geoid.toString());
                if (photoInfoList.size() > 0) {

                    String caseId = String.format("SELECT caseid  FROM  t_task where id=%d", Integer.parseInt(taskid));
                    List<Map> listCaseId = toolsResposity.exeSqlList(caseId);
                    List tableContentList = new ArrayList();//表单内容
                    for (Map map1 : listCaseId) {
                        Integer caseid = (Integer) map1.get("caseid");
                        Map mapResult = queryTablesByGly(subtaskid.toString(), caseid.toString(), edit_geoid + "");
                        tableContentList.add(mapResult);
                    }
                    //改子任务已核实
                    String sql = String.format("SELECT id, subtaskid, taskid,st_astext(ST_Transform(t.geom,4326))  from t_subtask_geom t where subtaskid=%d and id=%d and taskid=%d", subtaskid, edit_geoid, Integer.parseInt(taskid));
                    List<Map> listGeom = toolsResposity.exeSqlList(sql);//
                    for (Map geoMap : listGeom) {
                        String geomStr = geoMap.get("st_astext").toString();
                        String geoStrResult = "";
                        if (geomStr.contains("MULTIPOLYGON")) {
                            geoStrResult = geomStr.replace("MULTIPOLYGON(((", "")
                                    .replace(" )))", "").replace(")))", "");
                        } else if (geomStr.contains("POLYGON")) {
                            geoStrResult = geomStr.replace("POLYGON((", "")
                                    .replace(" ))", "").replace("))", "");
                        }
                        geoMap.put("geo", geoStrResult);
                    }
                    //添加视频
                    List listVideos = queryVideoFile(subtaskid + "", edit_geoid + "");
                    Map tempMap = new HashMap();
                    tempMap.put("tableContent", tableContentList);
                    tempMap.put("photos", photoInfoList);
                    tempMap.put("videos", listVideos);
                    tempMap.put("geom", listGeom);
                    resultList.add(tempMap);
                }
            }
        }
        return resultList;
    }

    /**
     * @return java.util.List
     * @Author zxw
     * @Description //TODO  修改 原来queryTableFile接口，原service层未动
     * @Date 2023/10/24  14:03   [subtaskid, edit_geoid]
     **/
    private List queryTableFileByGly(String subtaskid, String edit_geoid) {
        List returnList = new ArrayList();

        String base64 = null;

        String s = "";
        try {
            //根据id查询文件metadata.globle_gid
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid);

            Query query = new Query(criteria);
            GridFSFindIterable gridFSFiles =
                    gridFsTemplate.find(query);


            for (GridFSFile gridFSFile : gridFSFiles) {
                Map returnMap = new HashMap();
                Document metadataDocument = gridFSFile.getMetadata();

                String formcontentname = metadataDocument.get("globle_formcontentname").toString();
                String filename = metadataDocument.get("filename").toString();


                String lon = metadataDocument.get("lon").toString();
                String lat = metadataDocument.get("lat").toString();

                String alpha = metadataDocument.get("alpha").toString();
                String beta = metadataDocument.get("beta").toString();
                String gamma = metadataDocument.get("gamma").toString();

                MongoDatabase mongoDatabase = mongoTemplate.getDb();
                GridFSBucket gridFSBucket = GridFSBuckets.create(mongoDatabase);
                //打开流下载对象
                GridFSDownloadStream downloadStream =
                        gridFSBucket.openDownloadStream(gridFSFile.getObjectId());
                //获取流对象
                GridFsResource gridFsResource = new GridFsResource(gridFSFile, downloadStream);
                //添加日期目录
                String format = sdf.format(new Date());
                // 使用时间戳确保唯一
                String uniqueFolderName = format + "_" + System.currentTimeMillis();
                File fileDir = new File(tempJpgFile + uniqueFolderName);
                //指定本地文件夹存储图片
                String filePath = fileDir.getAbsolutePath() + File.separator + "tempGly.jpg";

                File targetFile = new File(filePath);
                FileUtils.copyInputStreamToFile(gridFsResource.getInputStream(), targetFile);

                try {
                    base64 = convertFileToBase64(filePath);
                    returnMap.put("image", base64);
                    returnMap.put("formcontentname", formcontentname);
                    returnMap.put("filename", filename);
                    returnMap.put("alpha", alpha);
                    returnMap.put("beta", beta);
                    returnMap.put("gamma", gamma);
                    returnMap.put("lon", lon);
                    returnMap.put("lat", lat);
                    returnList.add(returnMap);
                    targetFile.delete();
                    fileDir.delete();
                } catch (Exception e) {
                    e.printStackTrace();
                }
//                System.out.println("打印文件:" + s);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnList;
    }

    private Map queryTablesByGly(String subtaskid, String caseid, String edit_geoid) {

//        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
//        Long userid = jwtUser.getId();

        Map returnMap = new HashMap();
        //结构
        String sql = "select * from t_form_content where formid in (SELECT formid FROM public.t_case_form where caseid = '" + caseid + "')";
        List structList = toolsResposity.exeSqlList(sql);
        returnMap.put("struct", structList);

        //内容
        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.globle_edit_geoid").is(edit_geoid);
        Query query = new Query(criteria);
        List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");
        if (contentList.size() == 0) {
            returnMap.put("hasContent", false);
        } else {
            returnMap.put("hasContent", true);
        }
        returnMap.put("content", contentList);

        return returnMap;
    }

    @Override
    public void deleteVideoFile(String subtaskid, String filename) {
        String delSql = String.format("DELETE FROM t_video_info WHERE subtaskid='%s' and file_name='%s'", subtaskid, filename);
        toolsResposity.exeSql(delSql);
    }


    private List queryTableFileByGlyAndTime(LocalDateTime startTime, LocalDateTime endTime, String subtaskid, String edit_geoid) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String formattedStartTime = startTime.format(formatter);
        String formattedEndTime = endTime.format(formatter);

        List returnList = new ArrayList();

        String base64 = null;

        String s = "";
        try {
            //根据id查询文件metadata.globle_gid
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid).and("metadata.time").gte(formattedStartTime).lte(formattedEndTime);

            Query query = new Query(criteria);
            GridFSFindIterable gridFSFiles = gridFsTemplate.find(query);

            for (GridFSFile gridFSFile : gridFSFiles) {
                Map returnMap = new HashMap();
                Document metadataDocument = gridFSFile.getMetadata();

                String formcontentname = metadataDocument.get("globle_formcontentname").toString();
                String filename = metadataDocument.get("filename").toString();

                String lon = metadataDocument.get("lon").toString();
                String lat = metadataDocument.get("lat").toString();

                String alpha = metadataDocument.get("alpha").toString();
                String beta = metadataDocument.get("beta").toString();
                String gamma = metadataDocument.get("gamma").toString();
                String time = metadataDocument.get("time").toString();

                MongoDatabase mongoDatabase = mongoTemplate.getDb();
                GridFSBucket gridFSBucket = GridFSBuckets.create(mongoDatabase);
                //打开流下载对象
                GridFSDownloadStream downloadStream =
                        gridFSBucket.openDownloadStream(gridFSFile.getObjectId());
                //获取流对象
                GridFsResource gridFsResource = new GridFsResource(gridFSFile, downloadStream);

                //添加日期目录
                String format = sdf.format(new Date());
                // 使用时间戳确保唯一
                String uniqueFolderName = format + "_" + System.currentTimeMillis();
                File fileDir = new File(tempJpgFile + uniqueFolderName);
                //指定本地文件夹存储图片
                String filePath = fileDir.getAbsolutePath() + File.separator + "temp2.jpg";

                File targetFile = new File(filePath);
                FileUtils.copyInputStreamToFile(gridFsResource.getInputStream(), targetFile);

                try {
                    base64 = convertFileToBase64(filePath);
                    returnMap.put("image", base64);
                    returnMap.put("formcontentname", formcontentname);
                    returnMap.put("filename", filename);
                    returnMap.put("alpha", alpha);
                    returnMap.put("beta", beta);
                    returnMap.put("gamma", gamma);
                    returnMap.put("lon", lon);
                    returnMap.put("lat", lat);
                    returnMap.put("time", time);
                    returnList.add(returnMap);

                    //用这个强制删除  zxw
                    FileUtils.deleteDirectory(fileDir);

                } catch (Exception e) {
                    e.printStackTrace();
                }
//                System.out.println("打印文件:" + s);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //展示面板显示未完成任务的polygon——2024.04.08张所新提出需求
     * @Date 9:33 2024/4/10
     **/
    @Override
    public List<SubtaskGeom> queryUnCheckedByTaskId(Integer taskid) {
        SubtaskGeom subtaskGeom = new SubtaskGeom();
        subtaskGeom.setTaskid(taskid);
        return mobileMapper.queryUnCheckedByTaskId(subtaskGeom);
    }


}

